#!/bin/bash

# Ubuntu 22.04 SQL Server驱动安装脚本
# 用于解决pymssql和pyodbc连接SQL Server的问题

echo "🔧 开始安装SQL Server驱动程序..."

# 更新系统包
echo "📦 更新系统包..."
sudo apt update

# 安装基础依赖
echo "🔨 安装基础依赖..."
sudo apt install -y curl apt-transport-https gnupg lsb-release

# 1. 安装Microsoft ODBC Driver for SQL Server
echo "🔧 安装Microsoft ODBC Driver for SQL Server..."

# 添加Microsoft GPG密钥
if ! curl -sSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg; then
    echo "❌ 添加Microsoft GPG密钥失败"
    exit 1
fi

# 添加Microsoft Ubuntu 22.04存储库
if ! echo "deb [arch=amd64,arm64,armhf signed-by=/usr/share/keyrings/microsoft-prod.gpg] https://packages.microsoft.com/ubuntu/22.04/prod jammy main" | sudo tee /etc/apt/sources.list.d/mssql-release.list; then
    echo "❌ 添加Microsoft存储库失败"
    exit 1
fi

# 更新包列表
sudo apt update

# 安装ODBC Driver 17和18
echo "📥 安装ODBC Driver 17 for SQL Server..."
sudo ACCEPT_EULA=Y apt install -y msodbcsql17

echo "📥 安装ODBC Driver 18 for SQL Server..."
sudo ACCEPT_EULA=Y apt install -y msodbcsql18 || echo "⚠️ ODBC Driver 18安装失败，继续使用Driver 17"

# 安装unixODBC开发包
echo "📥 安装unixODBC开发包..."
sudo apt install -y unixodbc-dev

# 2. 安装FreeTDS (用于pymssql)
echo "🔧 安装FreeTDS..."
sudo apt install -y freetds-dev freetds-bin

# 3. 配置FreeTDS
echo "⚙️ 配置FreeTDS..."
sudo tee /etc/freetds/freetds.conf > /dev/null << 'EOF'
[global]
    tds version = 7.4
    timeout = 20
    connect timeout = 20
    client charset = UTF-8
    text size = 4294967295
    
[sqlserver]
    host = fast9.shenzhuo.vip
    port = 56472
    tds version = 7.4
EOF

# 4. 验证安装
echo "🔍 验证驱动安装..."

echo "检查ODBC驱动:"
odbcinst -j

echo "检查已安装的驱动:"
odbcinst -q -d

echo "检查FreeTDS配置:"
tsql -C

# 5. 测试连接
echo "🧪 测试数据库连接..."

# 创建Python测试脚本
cat > /tmp/test_connection.py << 'EOF'
#!/usr/bin/env python3

import sys

def test_pymssql():
    """测试pymssql连接"""
    try:
        import pymssql
        print("✅ pymssql模块导入成功")
        
        conn = pymssql.connect(
            server='fast9.shenzhuo.vip',
            port=56472,
            user='sa',
            password='shanks00',
            database='GP',
            timeout=20,
            login_timeout=20,
            tds_version='7.4'
        )
        print("✅ pymssql连接成功")
        conn.close()
        return True
    except ImportError:
        print("❌ pymssql模块未安装")
        return False
    except Exception as e:
        print(f"❌ pymssql连接失败: {e}")
        return False

def test_pyodbc():
    """测试pyodbc连接"""
    try:
        import pyodbc
        print("✅ pyodbc模块导入成功")
        
        # 测试ODBC Driver 17
        conn_str = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=fast9.shenzhuo.vip,56472;DATABASE=GP;UID=sa;PWD=shanks00;Encrypt=no;TrustServerCertificate=yes;"
        conn = pyodbc.connect(conn_str, timeout=20)
        print("✅ pyodbc (ODBC Driver 17) 连接成功")
        conn.close()
        return True
    except ImportError:
        print("❌ pyodbc模块未安装")
        return False
    except Exception as e:
        print(f"❌ pyodbc连接失败: {e}")
        # 尝试ODBC Driver 18
        try:
            conn_str = "DRIVER={ODBC Driver 18 for SQL Server};SERVER=fast9.shenzhuo.vip,56472;DATABASE=GP;UID=sa;PWD=shanks00;Encrypt=no;TrustServerCertificate=yes;"
            conn = pyodbc.connect(conn_str, timeout=20)
            print("✅ pyodbc (ODBC Driver 18) 连接成功")
            conn.close()
            return True
        except Exception as e2:
            print(f"❌ pyodbc (ODBC Driver 18) 连接也失败: {e2}")
            return False

if __name__ == "__main__":
    print("🧪 测试SQL Server连接...")
    
    pymssql_ok = test_pymssql()
    pyodbc_ok = test_pyodbc()
    
    if pymssql_ok or pyodbc_ok:
        print("🎉 至少一个驱动连接成功!")
        sys.exit(0)
    else:
        print("❌ 所有驱动连接都失败")
        sys.exit(1)
EOF

# 运行测试（如果在Python虚拟环境中）
if [ -f ".venv/bin/python" ]; then
    echo "🐍 使用虚拟环境测试..."
    .venv/bin/python /tmp/test_connection.py
elif command -v python3 &> /dev/null; then
    echo "🐍 使用系统Python测试..."
    python3 /tmp/test_connection.py
else
    echo "⚠️ 未找到Python，请手动运行: python3 /tmp/test_connection.py"
fi

# 清理临时文件
rm -f /tmp/test_connection.py

echo "✅ SQL Server驱动安装完成!"
echo "💡 如果仍有问题，请检查防火墙设置或网络连接"
echo "💡 重启应用程序以确保驱动程序正确加载"